import pymysql


class ConnectMysql:
    def __init__(self):
        db = pymysql.connect(host="localhost", user='root', password='020308', database="xjgl", charset="utf8")
        cursor = db.cursor()
        sql = """SELECT * FROM student LEFT JOIN success ON student.s_no=success.s_no 
                        UNION SELECT * FROM student RIGHT JOIN success ON student.s_no=success.s_no ;
                    """
        cursor.execute(sql)
        results = cursor.fetchall()
        self.student_msg_list = []
        for row in results:
            student_dict = {}
            cursor.execute('SELECT d_name from department where d_no=%s', row[5])
            d_name = cursor.fetchone()[0]
            cursor.execute('SELECT m_name from major where m_no=%s', row[6])
            m_name = cursor.fetchone()[0]
            cursor.execute('SELECT c_name from class where c_no=%s', row[7])
            c_name = cursor.fetchone()[0]
            cursor.execute('SELECT co_name from course where c_no=%s', row[9])
            co_name = cursor.fetchone()[0]
            student_dict['学号'] = row[0]
            student_dict['姓名'] = row[1]
            student_dict['性别'] = row[2]
            student_dict['年龄'] = str(row[3])
            student_dict['出生日期'] = row[4].strftime('%Y-%m-%d')
            student_dict['学院'] = d_name
            student_dict['专业'] = m_name
            student_dict['班级'] = c_name
            student_dict['课程名称'] = co_name
            student_dict['分数'] = str(row[10])
            student_dict['学分'] = str(row[11])
            self.student_msg_list.append(student_dict)

    def get_student_msg(self, s_no):
        for msg_dict in self.student_msg_list:
            if msg_dict['学号'] == s_no:
                return msg_dict

    def get_student_msg_list(self):
        return self.student_msg_list

    def get_student_score(self, s_no):
        for msg_dict in self.student_msg_list:
            if msg_dict['学号'] == s_no:
                score_dict = {'学号': s_no,
                              '姓名': msg_dict['姓名'],
                              '课程名称': msg_dict['课程名称'],
                              '分数': msg_dict['分数'],
                              '学分': msg_dict['学分']
                              }
                return score_dict
